package com.wxy.service.impl;

import cn.hutool.core.bean.BeanUtil;
import com.wxy.config.GetConnectionConfig;
import com.wxy.eneity.ColumnMessage;
import com.wxy.eneity.IdConnection;
import com.wxy.eneity.NewColumn;
import com.wxy.eneity.RenameColumn;
import com.wxy.service.TableMessageService;
import com.wxy.service.TableService;
import org.apache.commons.dbutils.QueryRunner;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author 47868
 */
@Service("TableMessageService")
public class TableMessageServiceImpl implements TableMessageService {
    private final TableService tableService;
    @Autowired
    public TableMessageServiceImpl(TableService tableService) {
        this.tableService = tableService;
    }
    public static void closeConnection(Connection connection) {
        try {
            if(connection!=null && !connection.isClosed()){
                connection.close();
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }

    @Override
    public List<ColumnMessage> getTableStruct(IdConnection connection, String tableName) {
        return tableService.getColumnMessage(connection, tableName);
    }

    @Override
    public List<List<String>> getMessage(IdConnection connection, String tableName) {
        Connection conn = null;
        ResultSet rs = null;
        try {
            conn = GetConnectionConfig.getConnection(BeanUtil.toBean(connection,com.wxy.eneity.Connection.class));
            String sql = "select * from "+ tableName;
            rs = conn.createStatement().executeQuery(sql);
            ResultSetMetaData resultSetMetaData = rs.getMetaData();
            int columnCount = resultSetMetaData.getColumnCount();
            List<List<String>> list = new ArrayList<>();
            while (rs.next()) {
                List<String> one = new ArrayList<>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnData = rs.getString(i);
                    one.add(columnData);
                }
                list.add(one);
            }
            return list;
        }
        catch (Exception e) {
            System.out.println(e.getMessage());
        }
        finally {
            if(rs != null){
                try {
                    rs.close();
                }
                catch (Exception e) {
                    System.out.println(e.getMessage());
                }
            }
            closeConnection(conn);
        }
        return null;
    }

    @Override
    public String addColumn(IdConnection connection, String tableName, NewColumn newColumn) {
        Connection conn = null;
        try {
            conn = GetConnectionConfig.getConnection(BeanUtil.toBean(connection,com.wxy.eneity.Connection.class));
            String sql = "alter table " + tableName + " add column " + newColumn.getColumnName()+" "+newColumn.getColumnType()+"("+newColumn.getColumnSize()+")";
            conn.createStatement().execute(sql);
            return "ok";
        }
        catch (Exception e) {
            System.out.println(e.getMessage());
        }
        finally {
            if(conn != null){
                try {
                    conn.close();
                }
                catch (Exception e) {
                    System.out.println(e.getMessage());
                }
            }
        }
        return "error";
    }

    @Override
    public String deleteColumn(IdConnection connection, String tableName, String columnName) {
        Connection conn = null;
        try {
            conn = GetConnectionConfig.getConnection(BeanUtil.toBean(connection,com.wxy.eneity.Connection.class));
            String sql = "alter table " + tableName + " drop column " + columnName;
            conn.createStatement().execute(sql);
            return "ok";
        }
        catch (Exception e) {
            System.out.println(e.getMessage());
        }
        finally {
            TableServiceImpl.closeConnection(conn);
        }
        return "error";
    }

    @Override
    public String renameColumn(IdConnection connection, String tableName, RenameColumn renameColumn) {
        Connection conn = null;
        try {
            conn = GetConnectionConfig.getConnection(BeanUtil.toBean(connection,com.wxy.eneity.Connection.class));
            String sql = "alter table "+tableName+" rename column "+renameColumn.getOldColumnName()+" to "+renameColumn.getNewColumnName();
            System.out.println(sql);
            conn.createStatement().execute(sql);
            return "ok";
        }
        catch (Exception e) {
            System.out.println(e.getMessage());
        }
        finally {
            TableServiceImpl.closeConnection(conn);
        }
        return "error";
    }

    @Override
    public String addData(IdConnection connection,String tableName, Map<String, Object> data) {
        Connection conn = null;
        try {
            conn = GetConnectionConfig.getConnection(BeanUtil.toBean(connection,com.wxy.eneity.Connection.class));
            QueryRunner queryRunner = new QueryRunner();
            StringBuilder columns = new StringBuilder();
            StringBuilder placeholders = new StringBuilder();
            List<Object> values = new ArrayList<>();
            int index = 0;
            for (Map.Entry<String, Object> entry : data.entrySet()) {
                if (index > 0) {
                    columns.append(", ");
                    placeholders.append(", ");
                }
                columns.append(entry.getKey());
                placeholders.append("?");
                values.add(entry.getValue());
                index++;
            }
            String sql = "INSERT INTO " + tableName + " (" + columns + ") VALUES (" + placeholders + ")";
            int row = queryRunner.update(conn, sql, values.toArray());
            if (row > 0) {
                return "ok";
            }
            return "error";
        }
        catch (Exception e) {
            System.out.println(e.getMessage());
        }
        finally {
            TableServiceImpl.closeConnection(conn);
        }
        return "error";
    }

    @Override
    public String updateData(IdConnection connection, String tableName, Map<String, Object> data) {
        Connection conn = null;
        try {
            conn = GetConnectionConfig.getConnection(BeanUtil.toBean(connection,com.wxy.eneity.Connection.class));
            QueryRunner queryRunner = new QueryRunner();
            StringBuilder updateString = new StringBuilder();
            List<Object> lists = new ArrayList<>();
            String columnName=null;
            Object id= null;
            int index = 0;
            for (Map.Entry<String, Object> entry : data.entrySet()) {
                if (index == 0){
                    columnName = entry.getKey();
                    id = entry.getValue();
                    index++;
                    continue;
                }
                if (index > 1) {
                    updateString.append(", ");
                }
                updateString.append(entry.getKey());
                updateString.append("= ?");
                lists.add(entry.getValue());
                index++;
            }
            if (id != null) {
                lists.add(Integer.parseInt(id.toString()));
            }
            String sql = "update "+tableName+" set "+updateString+"where "+columnName+"= ?";
            int row = queryRunner.update(conn, sql, lists.toArray());
            if(row > 0){
                return "ok";
            }
            return "error";
        }
        catch (Exception e) {
            System.out.println(e.getMessage());
        }
        finally {
            TableServiceImpl.closeConnection(conn);
        }
        return "error";
    }

    @Override
    public String deleteRow(IdConnection connection, String tableName, String type, String name, Object id) {
        Connection conn = null;
        try {
            if(type.contains("INT")){
                id = Integer.parseInt(id.toString());
            }
            conn = GetConnectionConfig.getConnection(BeanUtil.toBean(connection,com.wxy.eneity.Connection.class));
            QueryRunner queryRunner = new QueryRunner();
            int row = queryRunner.update(conn, "delete from " + tableName + " where " + name+ " = ?", id);
            if (row > 0) {
                return "ok";
            }
            return "error";
        }
        catch (Exception e) {
            System.out.println(e.getMessage());
        }
        finally {
            TableServiceImpl.closeConnection(conn);
        }
        return "error";
    }

    @Override
    public String deleteAll(IdConnection connection, String tableName) {
        Connection conn = null;
        try {
            conn = GetConnectionConfig.getConnection(BeanUtil.toBean(connection,com.wxy.eneity.Connection.class));
            QueryRunner queryRunner = new QueryRunner();
            String sql = "delete from " + tableName;
            int row = queryRunner.update(conn, sql);
            if (row > 0) {
                return "ok";
            }
            return "error";
        }
        catch (Exception e) {
            System.out.println(e.getMessage());
        }
        finally {
            TableServiceImpl.closeConnection(conn);
        }
        return "error";
    }
}
